Motorcycle Part Sale Analysis

Parked motorcycle

You’re working for a company that sells motorcycle parts, and they’ve asked for some help in analyzing their sales data!

They operate three warehouses in the area, selling both retail and wholesale. They offer a variety of parts and accept credit cards, cash, and bank transfer as payment methods. However, each payment type incurs a different fee.

The board of directors wants to gain a better understanding of wholesale revenue by product line, and how this varies month-to-month and across warehouses. You have been tasked with calculating net revenue for each product line and grouping results by month and warehouse. The results should be filtered so that only "Wholesale" orders are included.

They have provided you with access to their database, which contains the following table called sales:

Sales

Column Data type Description
order_number VARCHAR Unique order number.
date DATE Date of the order, from June to August 2021.
warehouse VARCHAR The warehouse that the order was made from— North, Central, or West.
client_type VARCHAR Whether the order was Retail or Wholesale.
product_line VARCHAR Type of product ordered.
quantity INT Number of products ordered.
unit_price FLOAT Price per product (dollars).
total FLOAT Total price of the order (dollars).
payment VARCHAR Payment method—Credit card, Transfer, or Cash.
payment_fee FLOAT Percentage of total charged as a result of the payment method.

Your query output should be presented in the following format:

product_line month warehouse net_revenue
product_one
product_one
product_one
product_one
product_one
product_one
product_two
Code
import pandas as pd
import sqlite3
import sql
from itables import init_notebook_mode
Code
init_notebook_mode(all_interactive=True)
Code
df = pd.read_csv("sales.csv") 
conn = sqlite3.connect(":memory:")
Code
df.to_sql("sales", conn, index=False, if_exists="replace") 
1000
Code
# Load the SQL extension
%load_ext sql

# Register the existing in-memory SQLite connection in jupysql

%sql conn --alias sales_dataset

::: {#ff58f388 .cell customType=‘sql’ dataFrameVariableName=‘revenue_by_product_line’ executionCancelledAt=‘null’ executionTime=‘842’ initial=‘false’ integrationId=‘89e17161-a224-4a8a-846b-0adc0fe7a4b1’ lastExecutedAt=‘1703148380931’ lastScheduledRunId=‘null’ lastSuccessfullyExecutedCode=’– Start coding here

SELECT * FROM sales;’ execution_count=6}

Code
%%sql
SELECT *
FROM sales
Running query in 'sales_dataset'
index order_number date warehouse client_type product_line quantity unit_price total payment payment_fee
0 N1 2021-06-01T00:00:00.000 North Retail Breaking system 9 19.29 173.61 Cash 0.0
1 N2 2021-06-01T00:00:00.000 North Retail Suspension & traction 8 32.93 263.45 Credit card 0.03
2 N3 2021-06-01T00:00:00.000 North Wholesale Frame & body 16 37.84 605.44 Transfer 0.01
3 N4 2021-06-01T00:00:00.000 North Wholesale Suspension & traction 40 37.37 1494.8 Transfer 0.01
4 N5 2021-06-01T00:00:00.000 North Retail Frame & body 6 45.44 272.61 Credit card 0.03
5 N6 2021-06-02T00:00:00.000 North Retail Frame & body 1 40.41 40.41 Credit card 0.03
6 N7 2021-06-02T00:00:00.000 North Retail Miscellaneous 6 20.28 121.66 Credit card 0.03
7 N8 2021-06-03T00:00:00.000 North Retail Electrical system 9 20.5 184.54 Credit card 0.03
8 N9 2021-06-03T00:00:00.000 North Retail Suspension & traction 5 36.18 180.91 Credit card 0.03
9 N10 2021-06-03T00:00:00.000 North Retail Electrical system 5 28.33 141.67 Credit card 0.03
Truncated to displaylimit of 10.

:::

Code
%%sql
SELECT product_line,
    CASE 
        WHEN strftime('%m', date) = '06' THEN 'June'
        WHEN strftime('%m', date) = '07' THEN 'July'
        WHEN strftime('%m', date) = '08' THEN 'August'
    END AS month,
    warehouse,
    ROUND(SUM(total * (1 - payment_fee)), 2) AS net_revenue
FROM sales
WHERE client_type = 'Wholesale'
GROUP BY product_line, warehouse, month
ORDER BY product_line ASC, month ASC, net_revenue DESC;
Running query in 'sales_dataset'
product_line month warehouse net_revenue
Breaking system August Central 3009.1
Breaking system August West 2475.71
Breaking system August North 1753.19
Breaking system July Central 3740.94
Breaking system July West 3030.39
Breaking system July North 2568.55
Breaking system June Central 3648.14
Breaking system June North 1472.93
Breaking system June West 1200.64
Electrical system August North 4673.99
Truncated to displaylimit of 10.
Back to top